package org.codefinger.test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.codefinger.dao.DaoType;
import org.codefinger.dao.DaoUtil;
import org.codefinger.dao.PojoBuilder;
import org.codefinger.dao.Query;
import org.codefinger.dao.QueryCallback;
import org.codefinger.dao.QueryChain;
import org.codefinger.dao.QueryUtil;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Log4jFilter;
import com.alibaba.druid.pool.DruidDataSource;

public class DaoUtilDemo {

	public static void main(String[] args) {
		// 前面这里采用阿里巴巴的数据库连接池，主要是为了演示执行的SQL语句(您可以采用其它数据连接池)
		DruidDataSource dataSource = new DruidDataSource();
		Log4jFilter log4jFilter = new Log4jFilter();
		log4jFilter.setResultSetLogEnabled(false);
		log4jFilter.setStatementLogEnabled(false);
		log4jFilter.setStatementExecutableSqlLogEnable(true);
		Filter filter = log4jFilter;
		dataSource.setProxyFilters(Arrays.asList(filter));

		// 这里采用MySQL进行演示，您也可以选择SQLServer或Oracle
		dataSource.setUrl("jdbc:mysql://192.168.189.135:3306/codefinger?useUnicode=true&characterEncoding=utf-8");
		dataSource.setUsername("root");
		dataSource.setPassword("root");

		// 1.这里是真正开始，创建数据库工具
		DaoUtil daoUtil = new DaoUtil();
		daoUtil.setDataSource(dataSource);

		// 2.然后您就可以创建查询对象了
		Query query = daoUtil.createQuery("select * from customer where f_name left like :name and f_age > :age");

		// 3.进行查询
		query.putParam("name", "张三").getMapList();
		/*
		 * 这时，工具自动进行了如下查询（只用了其中一个查询条件）：
		 * 
		 * SELECT 
		 * 		CUSTOMER.F_ID, 
		 * 		CUSTOMER.F_NAME, 
		 * 		CUSTOMER.F_AGE,
		 * 		CUSTOMER.F_MONEY 
		 * FROM 
		 * 		CUSTOMER 
		 * WHERE 
		 * 		F_NAME LIKE '张三%'
		 */

		query.putParam("age", 15).getMapList();
		/*
		 * 这时，工具自动进行了如下查询（这次用了另一个查询条件）：
		 * 
		 * SELECT 
		 * 		CUSTOMER.F_ID, 
		 * 		CUSTOMER.F_NAME, 
		 * 		CUSTOMER.F_AGE,
		 * 		CUSTOMER.F_MONEY 
		 * FROM 
		 * 		CUSTOMER 
		 * WHERE 
		 * 		F_AGE > 15
		 */

		query.putParam("name", "张三").putParam("age", 15).getMapList();
		/*
		 * 这时，工具自动进行了如下查询（这次两个查询条件都同时利用了）：
		 * 
		 * SELECT 
		 * 		CUSTOMER.F_ID, 
		 * 		CUSTOMER.F_NAME, 
		 * 		CUSTOMER.F_AGE,
		 * 		CUSTOMER.F_MONEY 
		 * FROM 
		 * 		CUSTOMER 
		 * WHERE 
		 * 		F_NAME LIKE '张三%' 
		 * 		AND F_AGE > 15
		 */

		// 4.您可以试试更复杂的查询条件
		query = daoUtil.createQuery("select * from customer where f_name left like :name and (f_age >= :minAge or f_age <= :maxAge) order by f_age,f_name desc");

		query.putParam("minAge", 15)// 只根据最小年龄进行筛选
				.setOrders(0)// 只根据年龄进行排序
				.getMapList();
		/*
		 * 这时，工具自动进行了如下查询（是不是很智能）：
		 * 
		 * SELECT 
		 * 		CUSTOMER.F_ID, 
		 * 		CUSTOMER.F_NAME, 
		 * 		CUSTOMER.F_AGE,
		 * 		CUSTOMER.F_MONEY 
		 * FROM 
		 * 		CUSTOMER 
		 * WHERE 
		 * 		F_AGE >= 15 
		 * ORDER BY 
		 * 		F_AGE ASC
		 */

		// 刚才上面的掩饰采用的就是命名参数，这里采用顺序参数
		query = daoUtil.createQuery("select * from customer where f_name all like ? and (f_age >= ? or f_age <= ?) order by f_age,f_name desc");

		query.setParams("张三", null, 20)// 只根据姓名和最大年龄进行筛选
				.setOrders(1)// 只根据姓名进行排序
				.getMapList();
		/*
		 * 这时，工具自动进行了如下查询（真的很智能！）：
		 * 
		 * SELECT 
		 * 		CUSTOMER.F_ID, 
		 * 		CUSTOMER.F_NAME, 
		 * 		CUSTOMER.F_AGE,
		 * 		CUSTOMER.F_MONEY 
		 * FROM 
		 * 		CUSTOMER 
		 * WHERE 
		 * 		F_NAME LIKE '%张三%' 
		 * 		AND F_AGE <= 20 
		 * ORDER BY 
		 * 		F_NAME DESC
		 */

		// @formatter:off
		// 刚才上面的SQL语句还是太简单了，来点复杂的看看
		query = daoUtil.createQuery(
				"select " + 
						"A.f_id id," + 
						"B.f_name as name," + 
						"C.f_age age," + 
						"D.f_money as money " + 
				"from " + 
						"customer A " + 
						"inner join customer B on B.f_id = A.f_id " + 
						"left join customer C on C.f_id = B.f_id " + 
						"inner join customer D on D.f_id = C.f_id " + 
				"where " + 
						"(A.f_age >= :minAge or A.f_age <= :minAge) " + 
						"and B.f_name left like :leftName " + 
						"and C.f_name all like :allName " + 
						"and D.f_money not in (3.5,3.6,:moneyNotIn) " + 
						"and D.f_age is not null " + 
						"and D.f_age = (select f_age from customer where f_age != :notAge) " + 
				"group by " + 
						"A.*,B.*,C.*,D.* " + 
				"having " + 
						"AVG(D.f_money) > 1000 " + 
				"union all " + 
						"select " + 
							"f_id id," + 
							"f_name name," + 
							"f_age as age," + 
							"f_money money " + 
						"from customer " + 
				"order by " + 
						"money desc,age asc");
		// @formatter:on

		query.putParam("notAge", 25) // 年龄不等于25
				.putParam("moneyNotIn", Arrays.asList(3.7, 9.125, 10000.9)) // Money不包含列表中的值
				.putParam("allName", "王") // 模糊查询，姓名中包含‘王’的
				.putParam("minAge", 10)// 最小年龄
				.setOrders(0) // 只按照Money排序
				.getMapSet();

		/*
		 * 就算是这么复杂的SQL语句，也能够被智能分析出来：
		 * 
		 * SELECT
		 * 		A.F_ID AS ID,
		 * 		B.F_NAME AS NAME, 
		 * 		C.F_AGE AS AGE, 
		 * 		D.F_MONEY AS MONEY 
		 * FROM 
		 * 		CUSTOMER A 
		 * INNER JOIN CUSTOMER B ON B.F_ID = A.F_ID 
		 * LEFT JOIN CUSTOMER C ON C.F_ID = B.F_ID 
		 * INNER JOIN CUSTOMER D ON D.F_ID = C.F_ID 
		 * WHERE 
		 * 		(A.F_AGE >= 10 OR A.F_AGE <= 10) 
		 * 		AND C.F_NAME LIKE '%王%'
		 * 		AND D.F_MONEY NOT IN (3.5, 3.6, 3.7, 9.125, 10000.9) 
		 * 		AND D.F_AGE IS NOT NULL 
		 * 		AND D.F_AGE = (SELECT F_AGE FROM CUSTOMER WHERE F_AGE != 25)
		 * GROUP BY 
		 * 		A.F_ID, A.F_NAME, A.F_AGE, A.F_MONEY, B.F_ID, B.F_NAME,
		 * 		B.F_AGE, B.F_MONEY, C.F_ID, C.F_NAME, C.F_AGE, C.F_MONEY, D.F_ID,
		 * 		D.F_NAME, D.F_AGE, D.F_MONEY 
		 * HAVING 
		 * 		AVG(DISTINCT D.F_MONEY) > 1000
		 * UNION ALL 
		 * 		SELECT 
		 * 			F_ID AS ID,
		 * 			F_NAME AS NAME, 
		 * 			F_AGE AS AGE, 
		 * 			F_MONEY AS MONEY 
		 * 		FROM CUSTOMER 
		 * ORDER BY 
		 * 		MONEY DESC
		 */

		// 让我们看看查询Count和分页有多简单
		QueryChain queryChain = query.putParam("notAge", 25) // 年龄不等于25
				.putParam("moneyNotIn", Arrays.asList(3.7, 9.125, 10000.9)) // Money不包含列表中的值
				.putParam("allName", "王") // 模糊查询，姓名中包含‘王’的
				.putParam("minAge", 10)// 最小年龄
				.setOrders(0) // 只按照Money排序
				.setPage(5, 10); // 查第5页，每页显示10条
		// Count查询

		queryChain.queryCount();
		/*
		 * SELECT 
		 * 		COUNT(1) 
		 * FROM 
		 * 	(
		 * 		SELECT 
		 * 			A.F_ID AS ID, 
		 * 			B.F_NAME AS NAME, 
		 * 			C.F_AGE AS AGE, 
		 * 			D.F_MONEY AS MONEY 
		 * 		FROM 
		 * 			CUSTOMER A 
		 * 		INNER JOIN CUSTOMER B ON B.F_ID = A.F_ID 
		 * 		LEFT JOIN CUSTOMER C ON C.F_ID = B.F_ID 
		 * 		INNER JOIN CUSTOMER D ON D.F_ID = C.F_ID 
		 * 		WHERE 
		 * 			(A.F_AGE >= 10 OR A.F_AGE <= 10) 
		 * 			AND C.F_NAME LIKE '%王%'
		 * 			AND D.F_MONEY NOT IN (3.5, 3.6, 3.7, 9.125, 10000.9) 
		 * 			AND D.F_AGE IS NOT NULL 
		 * 			AND D.F_AGE = (SELECT F_AGE FROM CUSTOMER WHERE F_AGE != 25)
		 * 		GROUP BY 
		 * 			A.F_ID, A.F_NAME, A.F_AGE, A.F_MONEY, B.F_ID, B.F_NAME,
		 * 			B.F_AGE, B.F_MONEY, C.F_ID, C.F_NAME, C.F_AGE, C.F_MONEY, D.F_ID,
		 * 			D.F_NAME, D.F_AGE, D.F_MONEY 
		 * 		HAVING 
		 * 			AVG(DISTINCT D.F_MONEY) > 1000
		 * 		UNION ALL 
		 * 			SELECT 
		 * 				F_ID AS ID, F_NAME AS NAME, F_AGE AS AGE, F_MONEY AS  MONEY 
		 * 			FROM 
		 * 				CUSTOMER
		 * 	) ALL_CONTENT
		 */

		// 分页查询
		queryChain.getMapList();
		/*
		 * SELECT 
		 * 		A.F_ID AS ID, 
		 * 		B.F_NAME AS NAME, 
		 * 		C.F_AGE AS AGE, 
		 * 		D.F_MONEY AS MONEY 
		 * FROM 
		 * 		CUSTOMER A 
		 * INNER JOIN CUSTOMER B ON B.F_ID = A.F_ID 
		 * LEFT JOIN CUSTOMER C ON C.F_ID = B.F_ID 
		 * INNER JOIN CUSTOMER D ON D.F_ID = C.F_ID 
		 * WHERE 
		 * 		(A.F_AGE >= 10 OR A.F_AGE <= 10) 
		 * 		AND C.F_NAME LIKE '%王%'
		 * 		AND D.F_MONEY NOT IN (3.5, 3.6, 3.7, 9.125, 10000.9) 
		 * 		AND D.F_AGE IS NOT NULL 
		 * 		AND D.F_AGE = (SELECT F_AGE FROM CUSTOMER WHERE F_AGE != 25)
		 * GROUP BY 
		 * 		A.F_ID, A.F_NAME, A.F_AGE, A.F_MONEY, B.F_ID, B.F_NAME,
		 * 		B.F_AGE, B.F_MONEY, C.F_ID, C.F_NAME, C.F_AGE, C.F_MONEY, D.F_ID,
		 * 		D.F_NAME, D.F_AGE, D.F_MONEY 
		 * HAVING 
		 * 		AVG(DISTINCT D.F_MONEY) > 1000
		 * UNION ALL 
		 * 		SELECT 
		 * 			F_ID AS ID, F_NAME AS NAME, F_AGE AS AGE, F_MONEY AS  MONEY 
		 * 		FROM 
		 * 			CUSTOMER 
		 * ORDER BY 
		 * 		MONEY DESC 
		 * LIMIT 40, 10
		 */

		// 除了上面用到过的getMapList,还有其它可选的返回值类型

		// 您可以返回List泛型
		queryChain.getList(Customer.class);

		// 当您确定返回值只有一行数据时，您可以返回Pojo类型
		queryChain.fetch(Customer.class);

		// 当您确定返回值只有一行一列的时候，您可以这样
		int avg = daoUtil.createQuery("select sum(f_age) ageSum from customer").getUnique(DaoType.INT);
		System.out.println(avg);

		// 如果说您想要自己封装返回值类型，您可以这样
		queryChain.getResult(new QueryCallback<List<Customer>>() {

			@Override
			public List<Customer> getResult(ResultSet resultSet, String[] names) throws SQLException {
				List<Customer> customers = new ArrayList<Customer>();
				PojoBuilder<Customer> builder = QueryUtil.getPojoBuilder(Customer.class, resultSet, names);
				while (resultSet.next()) {
					Customer customer = builder.nextPojo();
					customer.setF_name("我想自己修改返回值");
					customers.add(customer);
				}
				return customers;
			}

		});

		// 首先试试新增操作
		Customer customer = new Customer("张三", 19, 100);
		daoUtil.insert("customer", customer);
		System.out.println(customer.getF_id()); // 自动为Pojo对象生成了主键

		// 批量新增
		Customer[] customers = new Customer[] { //
		// 两个对象
				new Customer("李四", 18, 100), //
				new Customer("王五", 17, 99) //
		};
		daoUtil.insert("customer", customers);
		daoUtil.insert("customer", Arrays.asList(customers)); // 也可以使用集合

		// 也可以采用链式调用的方式做新增
		daoUtil.insertInto("customer").set("f_name = ?, f_age = 16,f_money = ?").execute("赵六", 105);

		daoUtil.insertInto("customer").set("f_name = :name, f_age = :age")//
				.putParam("name", "田七")//
				.putParam("age", 15)//
				.execute();

		// 然后试试修改操作
		customer.setF_money(200);
		daoUtil.update("customer", customer);
		daoUtil.update("customer", Arrays.asList(customer, customer)); // 同样支持批量修改

		daoUtil.updateFrom("customer")//
				.set("f_name = :newName, f_age = :newAge, f_money = 100")//
				.where("f_id = :oldName and f_age > :oldAge")//
				.putParam("newName", "新名字")//
				.putParam("newAge", 20).putParam("oldName", "旧名字")//
				.putParam("oldAge", 18)//
				.execute();

		// 删除操作
		daoUtil.deleteByID("customer", 18); // 通过主键删除
		daoUtil.deleteByID("customer", 18, 19, 20); // 批量删除

		daoUtil.deleteFrom("customer").where("f_name left like ?").execute("张三");// 名称像“张三%”的将被删除
		daoUtil.deleteFrom("customer").execute();// 删除“customer”表的所有记录
	}
}
